package top.majuan.study.study.dao;

import com.mysql.cj.x.protobuf.MysqlxCrud;
import top.majuan.study.study.entity.Tbsoftwaretype;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbsoftwaretypeDAO {
    public static final String INSERT = "insert into tb_software_type(type_name,description) values(?,?)";
    public static final String QUERY_BY_ID = "select id,type_name,description from tb_software_type where id = ?";
    public static final String QUERY_BY_NAME = "select * from tb_software_type where type_name = ?";
    public static final String QUERY = "select * from tb_software_type";
    public static final String DELETE = "delete from tb_software_type where id = ?";
    public static final String QUERY_COUNT_BY_ID = "select count(*) from tb_software where type_id = ?";
    public static final String UPDATE = "update tb_software_type set type_name = ?,description = ? where id = ?";

    public static int update(Tbsoftwaretype type) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        ps.setString(1, type.getType_name());
        ps.setString(2, type.getDescription());
        ps.setInt(3, type.getId());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static int insert(Tbsoftwaretype type) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setString(1, type.getType_name());
        ps.setString(2, type.getDescription());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static Tbsoftwaretype queryById(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        Tbsoftwaretype type = null;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            type = new Tbsoftwaretype();
            type.setId(rs.getInt("id"));
            type.setType_name(rs.getString("type_name"));
            type.setDescription(rs.getString("description"));
        }
        conn.close();
        return type;
    }

    public static Tbsoftwaretype queryByName(String name) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_NAME);
        ps.setString(1, name);
        Tbsoftwaretype type = null;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            type = new Tbsoftwaretype();
            type.setId(rs.getInt("id"));
            type.setType_name(rs.getString("type_name"));
            type.setDescription(rs.getString("description"));
        }
        conn.close();
        return type;
    }

    public static List<Tbsoftwaretype> query() throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        List<Tbsoftwaretype> list = new ArrayList<>();
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Tbsoftwaretype type = new Tbsoftwaretype();
            type.setId(rs.getInt("id"));
            type.setType_name(rs.getString("type_name"));
            type.setDescription(rs.getString("description"));
            list.add(type);
        }
        conn.close();
        return list;
    }
    public static int delete(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }
    public static int queryCountById(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_COUNT_BY_ID);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        conn.close();
        return count;
    }


}
